﻿-- =============================================
-- Author:		<Author,XFL>
-- Edit date: <Create Date,2012 02 01>
-- Description:	<Description, 
-- [人资管理]-[员工工资管理]-[生成工资] 
-- 相对原来的项目来说 多出一个 其他补助字段 QtAllowance，同时固定补助地段也已经统一
-- 2012 03 01 备份，原因是 修改了工龄的计算方法 
-- =============================================
CREATE PROCEDURE [dbo].[proc_Wage_Create_BackUp]
	(
		@CompanyId int,
		@CreateMonth nvarchar(7),
		@CreateUser nvarchar(50),
		@GUID_Cr nvarchar(50),
		@GUID_New nvarchar(50),
		@DEL_un  nvarchar(Max)
	)
AS
Begin
	Declare @IsExists int
	Select  @IsExists=Count(Id) From Employee_Wage Where CompanyId=@CompanyId and Convert(nchar(7),FaFangMonth,120)=@CreateMonth--单位上月工资数量

	Declare @IsExists_HRFlag_1 int
	Select  @IsExists_HRFlag_1=Count(Id) From  Employee_Wage Where HRFlag = 1 and CompanyId=@CompanyId and Convert(nchar(7),FaFangMonth,120)=@CreateMonth--单位上月工资数量 人资暂未审批

-- =============================================
--- @IsExists=0 说明 该单位上月没有生成工资数据【执行一次插入】
--- @IsExists=@IsExists_HRFlag_1 说明，存在工资数据，并且一条都没审核【删除全部，执行插入】
---	@IsExists>@IsExists_HRFlag_1 and @IsExists_HRFlag_1<>0 说明，存在工资数据，并且只审批了部分数据【删除未审查、插入全部、删除已审查数据】
---	除以上3种条件，暂时没有其他条件 
--- 20111226 XFL
-- =============================================

--一条工资数据都没有 @XFL
	if(@IsExists=0)
		Begin
			Insert Into Employee_Wage
			(
				UserName
				,EName
				,CompanyId
				,DepartmentId
				,DepName
				,ZhiWu
				,GradeId
				,GradeName
				,PostCategoryId
				,PostCategoryName
---10
				,PostId
				,PostName
				,ChuQinDays
				,BasicWage
				,PostWage
				,WorkWage
				,Jxyuan
				,Jxkou
				,JxWage
				,ManQin
---20
				,Allowance
				,FixedAllowance /*固定补助*/
				,QtAllowance   /*其他补助*/
				,TiCheng
				,MTiCheng
				,YjBonus
				,LsBonus
				,ChuQinKouKuan
				,BxKouKuan
				,ChiDaoKouKuan
				,YJKouKuan
				,OtherKouKuan
				,FaFangMonth
				,CreateName
				,CreateDate
				,HRFlag
				,FinanceFlag
				,StateId
				,Guid_str
			)
			Select
				e.UserName
				,e.EName
				,e.CompanyId
				,e.DepartmentId
				,ISNULL((Select DName From Department Where Id=e.DepartmentId),'---')
				,e.ZhiWu
				,e.GradeId
				,ISNULL((Select Title From Grade Where Id=e.GradeId),'---')
				,IsNull(e.PostCategory,0)
				,ISNULL((Select Title From Post_Category Where Id=e.PostCategory),'---')
---10				
				,e.PostId
				,ISNULL((Select PostName From Post Where Id=e.PostId),'---')
				,ISNULL((Select Sum(datediff(hour,KouKuanFromDate,KouKuanToDate)) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9)),0)--未出勤天数[小时计算@XFL]
				,ISNULL((Select Wage From Grade Where Id=e.GradeId),0)
				,ISNULL((Select PostWage From Post Where Id=e.PostId),0)
				,(datediff(dd,ISNULL(e.RuZhiDate,GetDate()),GetDate())/365*100)
				,ISNULL((Select JxWage From Post Where Id=e.PostId),0)--岗位绩效
				,0--扣除绩效
				,ISNULL((Select JxWage From Post Where Id=e.PostId),0)--实发绩效
				
				--满勤奖励开始
				,Case When
					ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName  and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(1,5,10)),0)<150 and 
					ISNULL((Select Count(Id) From Employee_Attendance Where UserName=e.UserName  and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9,11)),0)=0 
					Then ISNULL((Select ManQin From Post Where Id=e.PostId),0)
					Else 0 
				 End
				--满勤奖励结束
---20				
				--,ISNULL((Select Sum(Amount) From Employee_Allowance Where PostId=e.PostId and CategoryId=1 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Sum(ea.Amount) From Employee_Allowance ea Where ea.PostId=e.PostId and ea.CategoryId=0 and ea.pid=e.pid and ea.cid=e.cid),0)
				-- 上面地域补助 来自 Allowance 表 根据 省份 城市 职级
				,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = e.UserName),0)
				--,0--补助 之 固定补助 --后期添加@XFL  固定补助 修改为上面从表中获取
				,0--补助 之 其他补助 --后期添加@XFL
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=2 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=3 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=4 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,0--临时奖金
				,ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9,11)),0)--出勤扣款
				,0--保险扣款
				,ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(1,5,10)),0)--迟到扣款
				,0--业绩扣款
				,0--其他扣款
				,@CreateMonth
				,@CreateUser
				,GetDate()
				,1
				,1
				,Case When e.State<>5 Then 1 Else 2 End
				,@GUID_New
			From Employee e Where e.CompanyId=@CompanyId and (e.State<>5 Or Convert(nchar(7),LiZhiDate,120)>=@CreateMonth) and Convert(nchar(7),ISNULL(e.RuZhiDate,GetDate()),120)<=@CreateMonth and   UserName<>'20111568' --火星人
		End

---出现此种情况 说明 上月生成的工资数据 人资一条也没有审批 所以 应该删除当前数据并重新生成
else if(@IsExists=@IsExists_HRFlag_1)

---if(@IsExists=@IsExists_HRFlag_1) 开始
Begin
delete From  Employee_Wage Where HRFlag = 1 and CompanyId=@CompanyId and Convert(nchar(7),FaFangMonth,120)=@CreateMonth

			Insert Into Employee_Wage
			(
				UserName
				,EName
				,CompanyId
				,DepartmentId
				,DepName
				,ZhiWu
				,GradeId
				,GradeName
				,PostCategoryId
				,PostCategoryName
---10
				,PostId
				,PostName
				,ChuQinDays
				,BasicWage
				,PostWage
				,WorkWage
				,Jxyuan
				,Jxkou
				,JxWage
				,ManQin
---20
				,Allowance
				,FixedAllowance /*固定补助*/
				,QtAllowance   /*其他补助*/
				,TiCheng
				,MTiCheng
				,YjBonus
				,LsBonus
				,ChuQinKouKuan
				,BxKouKuan
				,ChiDaoKouKuan
				,YJKouKuan
				,OtherKouKuan
				,FaFangMonth
				,CreateName
				,CreateDate
				,HRFlag
				,FinanceFlag
				,StateId
				,Guid_str
			)
			Select
				e.UserName
				,e.EName
				,e.CompanyId
				,e.DepartmentId
				,ISNULL((Select DName From Department Where Id=e.DepartmentId),'---')
				,e.ZhiWu
				,e.GradeId
				,ISNULL((Select Title From Grade Where Id=e.GradeId),'---')
				,IsNull(e.PostCategory,0)
				,ISNULL((Select Title From Post_Category Where Id=e.PostCategory),'---')
---10				
				,e.PostId
				,ISNULL((Select PostName From Post Where Id=e.PostId),'---')
				,ISNULL((Select Sum(datediff(hour,KouKuanFromDate,KouKuanToDate)) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9)),0)--未出勤天数[小时计算@XFL]
				,ISNULL((Select Wage From Grade Where Id=e.GradeId),0)
				,ISNULL((Select PostWage From Post Where Id=e.PostId),0)
				,(datediff(dd,ISNULL(e.RuZhiDate,GetDate()),GetDate())/365*100)
				,ISNULL((Select JxWage From Post Where Id=e.PostId),0)--岗位绩效
				,0--扣除绩效
				,ISNULL((Select JxWage From Post Where Id=e.PostId),0)--实发绩效
				
				--满勤奖励开始
				,Case When
					ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName  and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(1,5,10)),0)<150 and 
					ISNULL((Select Count(Id) From Employee_Attendance Where UserName=e.UserName  and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9,11)),0)=0 
					Then ISNULL((Select ManQin From Post Where Id=e.PostId),0)
					Else 0 
				 End
				--满勤奖励结束
---20				
				--,ISNULL((Select Sum(Amount) From Employee_Allowance Where PostId=e.PostId and CategoryId=1 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Sum(ea.Amount) From Employee_Allowance ea Where ea.PostId=e.PostId and ea.CategoryId=0 and ea.pid=e.pid and ea.cid=e.cid),0)
				-- 上面地域补助 来自 Allowance 表 根据 省份 城市 职级
				,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = e.UserName),0)
				--,0--补助 之 固定补助 --后期添加@XFL  固定补助 修改为上面从表中获取
				,0--补助 之 其他补助 --后期添加@XFL
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=2 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=3 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=4 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,0--临时奖金
				,ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9,11)),0)--出勤扣款
				,0--保险扣款
				,ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(1,5,10)),0)--迟到扣款
				,0--业绩扣款
				,0--其他扣款
				,@CreateMonth
				,@CreateUser
				,GetDate()
				,1
				,1
				,Case When e.State<>5 Then 1 Else 2 End
				,@GUID_New
				From Employee e Where e.CompanyId=@CompanyId and (e.State<>5 Or Convert(nchar(7),LiZhiDate,120)>=@CreateMonth) and Convert(nchar(7),ISNULL(e.RuZhiDate,GetDate()),120)<=@CreateMonth and UserName<>'20111568' --火星人
		End
---if(@IsExists=@IsExists_HRFlag_1) 结束
---
else
---if(@IsExists>@IsExists_HRFlag_1 and @IsExists_HRFlag_1<>0) 开始 
---说明 当前状态是 生成该单位上月工资的数据中 只有部分通过了审批 ,所以:
---     1.应该删除未审批的数据
---     2.重新生成 工资的全部数据
---     3.根据 GUID_str 删除 冗余的工资数据
---     4.全部更新 GUID_str 达到统一
if(@IsExists>@IsExists_HRFlag_1 and @IsExists_HRFlag_1<>0)
begin 
---     1.获取人资已审批员工ID存入临时表 并  删除未审批的数据
		--EXEC('delete From  Employee_Wage Where HRFlag = 1 and CompanyId='+@CompanyId+ ' and Guid_str='+@GUID_Cr+' and username in ('+@DEL_un+')')
		declare @list_HRFlag2_username table( username  nvarchar(50))		
		;WITH list As
		(	
		SELECT UserName From Employee_Wage Where HRFlag=2 and CompanyId=@CompanyId  and  guid_str=@GUID_Cr
		)
		insert into @list_HRFlag2_username (username) select username From list  ;
		
		--删除		
		delete From  Employee_Wage where  HRFlag = 1 and CompanyId=@CompanyId and  guid_str=@GUID_Cr 

---     2.重新生成 工资的全部数据

			Insert Into Employee_Wage
			(
				UserName
				,EName
				,CompanyId
				,DepartmentId
				,DepName
				,ZhiWu
				,GradeId
				,GradeName
				,PostCategoryId
				,PostCategoryName
---10
				,PostId
				,PostName
				,ChuQinDays
				,BasicWage
				,PostWage
				,WorkWage
				,Jxyuan
				,Jxkou
				,JxWage
				,ManQin
---20
				,Allowance
				,FixedAllowance /*固定补助*/
				,QtAllowance   /*其他补助*/
				,TiCheng
				,MTiCheng
				,YjBonus
				,LsBonus
				,ChuQinKouKuan
				,BxKouKuan
				,ChiDaoKouKuan
				,YJKouKuan
				,OtherKouKuan
				,FaFangMonth
				,CreateName
				,CreateDate
				,HRFlag
				,FinanceFlag
				,StateId
				,Guid_str
			)
			Select
				e.UserName
				,e.EName
				,e.CompanyId
				,e.DepartmentId
				,ISNULL((Select DName From Department Where Id=e.DepartmentId),'---')
				,e.ZhiWu
				,e.GradeId
				,ISNULL((Select Title From Grade Where Id=e.GradeId),'---')
				,IsNull(e.PostCategory,0)
				,ISNULL((Select Title From Post_Category Where Id=e.PostCategory),'---')
---10				
				,e.PostId
				,ISNULL((Select PostName From Post Where Id=e.PostId),'---')
				,ISNULL((Select Sum(datediff(hour,KouKuanFromDate,KouKuanToDate)) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9)),0)--未出勤天数[小时计算@XFL]
				,ISNULL((Select Wage From Grade Where Id=e.GradeId),0)
				,ISNULL((Select PostWage From Post Where Id=e.PostId),0)
				,(datediff(dd,ISNULL(e.RuZhiDate,GetDate()),GetDate())/365*100)
				,ISNULL((Select JxWage From Post Where Id=e.PostId),0)--岗位绩效
				,0--扣除绩效
				,ISNULL((Select JxWage From Post Where Id=e.PostId),0)--实发绩效
				
				--满勤奖励开始
				,Case When
					ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName  and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(1,5,10)),0)<150 and 
					ISNULL((Select Count(Id) From Employee_Attendance Where UserName=e.UserName  and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9,11)),0)=0 
					Then ISNULL((Select ManQin From Post Where Id=e.PostId),0)
					Else 0 
				 End
				--满勤奖励结束
---20				
				--,ISNULL((Select Sum(Amount) From Employee_Allowance Where PostId=e.PostId and CategoryId=1 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Sum(ea.Amount) From Employee_Allowance ea Where ea.PostId=e.PostId and ea.CategoryId=0 and ea.pid=e.pid and ea.cid=e.cid),0)
				-- 上面地域补助 来自 Allowance 表 根据 省份 城市 职级
				,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = e.UserName),0)
				--,0--补助 之 固定补助 --后期添加@XFL  固定补助 修改为上面从表中获取
				,0--补助 之 其他补助 --后期添加@XFL
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=2 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=3 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,ISNULL((Select Amount From Employee_Allowance Where PostId=e.PostId and CategoryId=4 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=e.CompanyId)),0)
				,0--临时奖金
				,ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(2,3,4,6,7,8,9,11)),0)--出勤扣款
				,0--保险扣款
				,ISNULL((Select Sum(KouKuanAmount) From Employee_Attendance Where UserName=e.UserName and Convert(nchar(7),KouKuanToDate,120)=@CreateMonth and TypeId in(1,5,10)),0)--迟到扣款
				,0--业绩扣款
				,0--其他扣款
				,@CreateMonth
				,@CreateUser
				,GetDate()
				,1
				,1
				,Case When e.State<>5 Then 1 Else 2 End
				,@GUID_New
				From Employee e Where e.CompanyId=@CompanyId and (e.State<>5 Or Convert(nchar(7),LiZhiDate,120)>=@CreateMonth) and Convert(nchar(7),ISNULL(e.RuZhiDate,GetDate()),120)<=@CreateMonth and UserName<>'20111568' --火星人

---     3.根据 GUID_str 删除 冗余的工资数据 即 与  [HRFlag = 2] 冲突的数据

		delete From  Employee_Wage Where HRFlag =1 and CompanyId=@CompanyId and Guid_str=@GUID_New and username in (select D.username From @list_HRFlag2_username D)
		---print 'delete From  Employee_Wage Where HRFlag =1 and CompanyId='+convert(varchar,@CompanyId)+' and Guid_str='''+@GUID_New+''''

---     4.全部更新 GUID_str 达到统一
        update  Employee_Wage set guid_str=@GUID_New Where HRFlag = 2 and guid_str=@GUID_Cr

return 
end
---if(@IsExists>@IsExists_HRFlag_1 and @IsExists_HRFlag_1<>0) 结束
End
	RETURN
